package UnderAccessDBclass;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.Objects;
import java.util.Properties;

public class Druid_utils {
    //静态数据源
    //它负责建立与数据库的连接，当在应用程序中访问数据库时
    //不必编写连接数据库的代码，直接引用DataSource获取数据库的连接对象即可。
    // 用于获取操作数据Connection对象。
    private static DataSource ds;

    //静态代码块完成ds的初始化
    static {
        Properties properties = new Properties();
        try {
            String path = Objects.requireNonNull(Druid_utils.class.getResource("/")).getPath() + "druid.properties";
            properties.load(new FileInputStream(path));
            //创建一个指定参数的数据库连接池
            ds = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //getConnection方法
    public static Connection getConnection() {
        try {
            //从数据库连接池中获取一个连接,并返回
            return ds.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    //关闭连接
    //将使用的Connection放回连接池
    public static void close(Connection connection, Statement statement, ResultSet set) {
        //判断是否为null
        try {
            if (set != null) {
                set.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            //将编译异常转成运行异常抛出
            throw new RuntimeException(e);
        }
    }

    /**
     * 增删改的通用方法
     *
     * @param sql    SQL语句
     * @param params 需要注入的参数
     * @return 是否有影响的行数
     */
    public static boolean executeUpdate(String sql, Object[] params) {
        Connection con = getConnection();
        PreparedStatement pst = null;
        int n = 0;
        if (con != null) {
            try {
                pst = con.prepareStatement(sql);
                if (pst != null) {
                    //注入参数
                    for (int i = 0; i < params.length; i++) {
                        if (params[i] != null && params[i].getClass() == Date.class) {
                            pst.setTimestamp(i + 1, new Timestamp(((Date) params[i]).getTime() + 28800000L));
                        } else {
                            pst.setObject(i + 1, params[i]);
                        }
                    }
                    n = pst.executeUpdate();
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                close(con, pst, null);
            }
        }
        return n > 0;
    }

    /**
     * 查询单行数据的方法
     *
     * @param sql    SQL语句
     * @param params 需要注入的参数
     * @return 查询到的数据
     */
    public static ArrayList<Object[]> executeQuery(String sql, Object[] params) {
        //获取连接
        Connection con = getConnection();
        PreparedStatement pst = null;
        ResultSet rs = null;
        ArrayList<Object[]> list = null;
        if (con != null) {
            try {
                pst = con.prepareStatement(sql);
                if (pst != null) {
                    //注入参数
                    if (params != null && params.length > 0) {
                        for (int i = 0; i < params.length; i++) {
                            if (params[i] != null && params[i].getClass() == Date.class) {
                                pst.setTimestamp(i + 1, new Timestamp(((Date) params[i]).getTime() + 28800000L));
                            } else {
                                pst.setObject(i + 1, params[i]);
                            }
                        }
                    }

                    rs = pst.executeQuery();
                    if (rs != null) {
                        //获取结果集的元数据
                        ResultSetMetaData rsmd = rs.getMetaData();
                        //获取列数
                        int columnCount = rsmd.getColumnCount();
                        list = new ArrayList<>();
                        //获取每一列的数据
                        while (rs.next()) {
                            Object[] objects = new Object[columnCount];
                            for (int i = 0; i < columnCount; i++) {
                                //获取每一列的数据
                                objects[i] = rs.getObject(i + 1);
                            }
                            list.add(objects);
                        }
                    }
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                close(con, pst, rs);
            }
        }
        return list;
    }

    public static void main(String[] args) {
        String sql = "select * from student where sno=?";
        Object[] params = new Object[]{"201919"};
        ArrayList<Object[]> list = executeQuery(sql, params);
        for (Object[] objects : list) {
            for (Object object : objects) {
                System.out.print(object + " ");
            }
            System.out.println();
        }
    }
}
